package dao;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Properties;
import java.util.Vector;

import model.CommonList;

public class DaoMethod implements IResource_Dao
{
	private static Connection conn;
	private static Properties properties;
	private Savepoint savepoint;

	public static Connection getConnection()
	{
		if (conn == null)
		{
			String url = null;
			String username = null;
			String password = null;
			try
			{
				properties = getProperties();
				url = properties.getProperty("db_url");
				username = properties.getProperty("username");
				password = properties.getProperty("password");
				Class.forName("com.mysql.jdbc.Driver");
				conn = DriverManager.getConnection(url, username, password);
			}
			catch (ClassNotFoundException e)
			{
				e.printStackTrace();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		return conn;
	}

	public static Properties getProperties()
	{
		if (properties == null)
		{
			properties = new Properties();
			FileInputStream fs;
			try
			{
				fs = new FileInputStream("mydb.properties");
				properties.load(fs);
				fs.close();
			}
			catch (FileNotFoundException e)
			{
				e.printStackTrace();
			}
			catch (IOException e)
			{
				e.printStackTrace();
			}
		}
		return properties;
	}

	public Date getToday()
	{
		Calendar today = Calendar.getInstance();
		String month = (today.get(Calendar.MONTH) + 1) + "";
		String dayOfMonth = today.get(Calendar.DAY_OF_MONTH) + "";
		if (month.length() == 1)
		{
			month = "0" + month;
		}
		if (dayOfMonth.length() == 1)
		{
			dayOfMonth = "0" + dayOfMonth;
		}
		String date_today = today.get(Calendar.YEAR) + "-" + month + "-"
				+ dayOfMonth;
		return java.sql.Date.valueOf(date_today);
	}

	public static void recoveryResources(Statement st, PreparedStatement pst,
			ResultSet rs)
	{
		try
		{
			if (pst != null)
			{
				pst.close();
			}
		}
		catch (SQLException e1)
		{
			e1.printStackTrace();
		}
		try
		{
			if (rs != null)
			{
				rs.close();
			}
		}
		catch (SQLException e1)
		{
			e1.printStackTrace();
		}
		try
		{
			if (st != null)
			{
				st.close();
			}
		}
		catch (SQLException e1)
		{
			e1.printStackTrace();
		}
	}

	// public <T> Vector<Vector<Object>> pstQuery(String exe, T... t )
	public Vector<Vector<Object>> pstQuery(String exe, ArrayList<Object> param)
	{
		conn = getConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		Vector<Vector<Object>> result = new Vector<Vector<Object>>();
		try
		{
			pst = conn.prepareStatement(exe);
			for (int i = 0; i < param.size(); i++)
			{
				if (param.get(i) == null)
				{
					pst.setObject(i + 1, null);
				}
				else if (param.get(i) instanceof String)
				{
					pst.setString(i + 1, param.toString());
				}
				else if (param.get(i) instanceof Integer)
				{
					pst.setInt(i + 1, (Integer) param.get(i));
				}
				else if (param.get(i) instanceof Date)
				{
					pst.setDate(i + 1, (java.sql.Date) param.get(i));
				}
				else if (param.get(i) instanceof Float)
				{
					pst.setFloat(i + 1, (Float) param.get(i));
				}
			}
			rs = pst.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columns = rsmd.getColumnCount();
			if (rs.next())
			{
				do
				{
					Vector<Object> vec = new Vector<Object>();
					for (int j = 0; j < columns; j++)
					{
						vec.add(rs.getObject(j + 1));
					}
					result.add(vec);
				}
				while (rs.next());
			}
		}
		catch (SQLException e)
		{
			// errorInfo = e.getMessage();
			e.printStackTrace();
			CommonList.getInstance().setExcetionInfo(e.getMessage());
			return new Vector<Vector<Object>>();
		}
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
				}
			}
			catch (SQLException e1)
			{
				e1.printStackTrace();
			}
			try
			{
				if (pst != null)
				{
					pst.close();
				}
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		return result;
	}

	public int pstUpdate(String exe, ArrayList<Object> param)
	{
		conn = getConnection();
		PreparedStatement pst = null;
		int result = 0;
		try
		{
			pst = conn.prepareStatement(exe);
			for (int i = 0; i < param.size(); i++)
			{
				if (param.get(i) == null)
				{
					pst.setObject(i + 1, null);
				}
				else if (param.get(i) instanceof String)
				{
					pst.setString(i + 1, param.get(i).toString());
				}
				else if (param.get(i) instanceof Integer)
				{
					pst.setInt(i + 1, (Integer) param.get(i));
				}
				else if (param.get(i) instanceof Date)
				{
					pst.setDate(i + 1, (java.sql.Date) param.get(i));
				}
				else if (param.get(i) instanceof Float)
				{
					pst.setFloat(i + 1, (Float) param.get(i));
				}
			}
			result = pst.executeUpdate();
		}
		catch (SQLException e)
		{
			// errorInfo = e.getMessage();
			e.printStackTrace();
			CommonList.getInstance().setExcetionInfo(e.getMessage());
			return Integer.MIN_VALUE;
		}
		finally
		{
			try
			{
				if (pst != null)
				{
					pst.close();
				}
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		return result;
	}

	public Vector<Vector<Object>> stQuery(String exe)
	{
		conn = getConnection();
		Statement st = null;
		ResultSet rs = null;
		Vector<Vector<Object>> result = new Vector<Vector<Object>>();
		try
		{
			st = conn.createStatement();
			rs = st.executeQuery(exe);
			ResultSetMetaData rsmd = rs.getMetaData();
			int columns = rsmd.getColumnCount();
			if (rs.next())
			{
				do
				{
					Vector<Object> vec = new Vector<Object>();
					for (int j = 0; j < columns; j++)
					{
						vec.add(rs.getObject(j + 1));
					}
					result.add(vec);
				}
				while (rs.next());
			}
		}
		catch (SQLException e)
		{
			// errorInfo = e.getMessage();
			e.printStackTrace();
			CommonList.getInstance().setExcetionInfo(e.getMessage());
			return new Vector<Vector<Object>>();
		}
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
				}
			}
			catch (SQLException e1)
			{
				e1.printStackTrace();
			}
			try
			{
				if (st != null)
				{
					st.close();
				}
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		return result;
	}

	public int stUpdate(String exe)
	{
		conn = getConnection();
		Statement st = null;
		int result = 0;
		try
		{
			st = conn.createStatement();
			result = st.executeUpdate(exe);
		}
		catch (SQLException e)
		{
			// errorInfo = e.getMessage();
			CommonList.getInstance().setExcetionInfo(e.getMessage());
			 e.printStackTrace();
			return Integer.MIN_VALUE;
		}
		finally
		{
			try
			{
				if (st != null)
				{
					st.close();
				}
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		return result;
	}

	public synchronized int setConnectionAutoCommit(boolean b)
	{
		try
		{
			conn.setAutoCommit(b);
			if (!b)
			{
				savepoint = conn.setSavepoint();
			}
			return 0;
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			return Integer.MIN_VALUE;
		}
	}

	public int commit()
	{
		try
		{
			conn.commit();
			return 1;
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			return Integer.MIN_VALUE;
		}
	}

	public int rollBack()
	{
		try
		{
			conn.rollback(savepoint);
			return 1;
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			return Integer.MIN_VALUE;
		}
	}
}
